dbtプロジェクト構築に関する ベストプラクティス #2「ステージングデータ層(Staging Layer)に関する検討事項」 #dbt

アライアンス事業部 エンジニアグループ モダンデータスタック(MDS)チームのしんやです。

dbtはクラウド型データウェアハウス（DWH）におけるデータ変換に特化したツールです。非常に使い勝手が良く便利なツールである一方、様々な機能が提供されているのでいざ使ってみよう！となると『何をどうやって作り上げていけば良いんだろう？』『この場合のルールや制限はどういうものがあるの？どういう取り決めをもって扱えば良いんだろう？』という風に思うこともあるかと思います。(実際私自身そう感じました)

そんなユーザーの疑問や悩みを解決する、いわゆるdbtユーザー向けのガードレール的な存在となりうるコンテンツがdbt社から展開されています。それが『dbtベストプラクティスガイド(Best practice guides)』です。構造、スタイル、セットアップなど、dbt Labsの現在の視点を通した「ベストプラクティス」がまとめられています。

そこで当エントリでは、幾つか展開されている「dbtベストプラクティスガイド」で紹介されているコンテンツの中から『dbtプロジェクト構築』に関するもの、データウェアハウスにおける『ステージングデータ層(Staging Layer)』をどうdbtで構築していくかについて読み解いていきたいと思います。

目次

• はじめに
• ステージング層におけるファイルとフォルダの検討事項
  • フォルダ
  • ファイル名
• ステージング層におけるモデルの検討事項
• ステージング層におけるその他検討事項
  • ベースモデルの使用を検討するケース
  • ステージングテーブルの生成を自動化するCodegenパッケージを使用
  • ユーティリティフォルダの活用
  • dbtにおけるモデル開発のプロセス
• まとめ

ステージング層におけるファイルとフォルダの検討事項

下記はこのベストプラクティスシリーズで扱うjaffle_shopのdbtプロジェクト構成をツリー表示させたものです。当項で言及するステージングデータレイヤー層に相当するstagingフォルダ配下の要素行を強調表示させています。

jaffle_shop
├── README.md
├── analyses
├── seeds
│   └── employees.csv
├── dbt_project.yml
├── macros
│   └── cents_to_dollars.sql
├── models
│   ├── intermediate
│   │   └── finance
│   │       ├── _int_finance__models.yml
│   │       └── int_payments_pivoted_to_orders.sql
│   ├── marts
│   │   ├── finance
│   │   │   ├── _finance__models.yml
│   │   │   ├── orders.sql
│   │   │   └── payments.sql
│   │   └── marketing
│   │       ├── _marketing__models.yml
│   │       └── customers.sql
│   ├── staging
│   │   ├── jaffle_shop
│   │   │   ├── _jaffle_shop__docs.md
│   │   │   ├── _jaffle_shop__models.yml
│   │   │   ├── _jaffle_shop__sources.yml
│   │   │   ├── base
│   │   │   │   ├── base_jaffle_shop__customers.sql
│   │   │   │   └── base_jaffle_shop__deleted_customers.sql
│   │   │   ├── stg_jaffle_shop__customers.sql
│   │   │   └── stg_jaffle_shop__orders.sql
│   │   └── stripe
│   │       ├── _stripe__models.yml
│   │       ├── _stripe__sources.yml
│   │       └── stg_stripe__payments.sql
│   └── utilities
│       └── all_dates.sql
├── packages.yml
├── snapshots
└── tests
    └── assert_positive_value_for_total_amount.sql

この構成を踏まえて「フォルダ」と「ファイル」について見ていきます。

フォルダ

• dbtではフォルダ構造はとても重要。
• プロジェクトにエンコードされたナレッジグラフをより分かりやすく理解するための重要なインタフェースを担ってもいる。
• 多種多様なソースに準拠したモデルから、より少なくよりリッチなビジネスに準拠したモデルへと段階的にデータがどのように流れているかを反映したものであるべき。
• フォルダ分けをしておくことで、dbtではこのフォルダ構造を「選択の手段」として利用することが出来る。
  • Syntax overview | dbt Developer Hub
  • 例)上記構造例で「Stripeデータに関して構築された全てのモデルを実行」したい場合であればdbt build --select staging.stripe+とすることで一括実行が可能。

フォルダにおける「べき＆べからず」集

この記述を踏まえて「あれ、冒頭のプロジェクト構成にfinanceとかフォルダあったけどな..」と思ってましたがあれは中間データ層(Intermediate)、マート層(Mart)での話でしたね。ここでは「ステージング層(Staging)」では、ということです。

ファイル名

dbtでは一貫したファイル名のパターンを作成し展開することが重要です。ファイル名は全体で一意であり、ウェアハウスで選択／作成された時のモデル名と一致している必要があります。

ファイル名については様々な情報を出来るだけ明確に把握出来るように、以下の要素を含めておくことが望ましいです。

• モデルが存在するレイヤーのプレフィックス
• グループ化に関する情報
• モデル内のエンティティやトランスフォーメーションに関する具体的な情報

ファイル名における「べき＆べからず」集

ステージング層におけるモデルの検討事項

続いてのトピックは「ステージング層におけるモデル」の構造についてです。

下記ファイルは標準的なステージング層におけるモデル(stg_stripe__payments)の例です。sourceマクロを介してソーステーブルを読み込んでいるものと、SQLによる変換を行っているものが確認出来ます。

• Selecting from a source - Add sources to your DAG | dbt Developer Hub

このあとに続く変換レイヤーにおいてはモデルによって記述する内容は異なりますが、ステージング層におけるモデルの場合は基本的にこのパターンに全て従います。

with

source as (

    select * from {{ source('stripe','payment') }}

),

renamed as (

    select
        -- ids
        id as payment_id,
        orderid as order_id,

        -- strings
        paymentmethod as payment_method,
        case
            when payment_method in ('stripe', 'paypal', 'credit_card', 'gift_card') then 'credit'
            else 'cash'
        end as payment_type,
        status,

        -- numerics
        amount as amount_cents,
        amount / 100.0 as amount,

        -- booleans
        case
            when status = 'successful' then true
            else false
        end as is_completed_payment,

        -- dates
        date_trunc('day', created) as created_date,

        -- timestamps
        created::timestamp_ltz as created_at

    from source

)

select * from renamed

モデル定義における「べき＆べからず」集

models:
  jaffle_shop:
    staging:
      +materialized: view

ステージング層のモデルはソースマクロを使用する唯一の場所であり、ステージングモデルはソーステーブルと1:1の関係となる必要があります。これはすなわち、各ソースシステムテーブルに対し、それを参照する1つのステージング層のモデルがあり、後工程で使用するためのエントリーポイントとして機能する、ということになります。

ステージング層でモデルを上記のような形で定義しておくことで、コードをDRYに保つことが出来るようになります。再利用可能な構造としておくことで特定のコンポーネントモデルに対して常に実行したい変換をプッシュ出来るようになります(そして本来そうあるべきです)。同じ変換処理を複数回実行して複雑さを生じさせたり、計算リソースが無駄になるといった可能性もなくなります。後工程の様々なモデルで変換処理が必要となるのであればステージング層でそういった処理を済ませておくことは賢明な判断です。

• DRY原則（ドライげんそく）とは？ 意味・読み方・使い方をわかりやすく解説 - goo国語辞書

ステージング層におけるその他検討事項

ベースモデルの使用を検討するケース

場合によっては、クリーンでDRYなステージング層を維持するために、またビルディングブロックを確固たるコンセプトを作成するために、(ステージング層で)幾つかの結合を行う必要が出てくる可能性があります。

その場合、対象となるステージング層のディレクトリにサブディレクトリを作成し「ベースモデル」を用意することをお勧めします。これらは通常ステージング層にあるものとすべて同じプロパティを持ち、生データを直接取得して非結合変換を実行し、ステージング モデルで必要な基本モデルを結合します。

models/staging
├── jaffle_shop
│   ├── _jaffle_shop__docs.md
│   ├── _jaffle_shop__models.yml
│   ├── _jaffle_shop__sources.yml
│   ├── base
│   │   ├── base_jaffle_shop__customers.sql
│   │   └── base_jaffle_shop__deleted_customers.sql
│   ├── stg_jaffle_shop__customers.sql
│   └── stg_jaffle_shop__orders.sql
└── stripe
    ├── _stripe__models.yml
    ├── _stripe__sources.yml
    └── stg_stripe__payments.sql

ステージング フォルダーの下にベース レイヤーを構築する最も一般的な使用例は以下の通り。

✅ 任意のテーブルにおける「削除」に関するレコードが別テーブルとなっているような場合

場合によっては、ソースシステムが通常のテーブルと分けて「削除された(データを格納する)テーブル」を保存することがあります。こういった状況の場合、通常のテーブルを削除された(データを格納する)テーブルを結合させて扱う必要が出てきます(削除されたレコードをフィルタリング、またはマーキングするなど)。そんな場合は以下のように定義することで対応が可能です。

通常のテーブルをbaseフォルダ配下に配置、ファイル名も変更しSQLで定義：

with

source as (

    select * from {{ source('jaffle_shop','customers') }}

),

customers as (

    select
        id as customer_id,
        first_name,
        last_name

    from source

)

select * from customers

削除に関するテーブルも同様にbaseフォルダ配下に配置、ファイル名も変更しSQLで定義：

with

source as (

    select * from {{ source('jaffle_shop','customer_deletes') }}

),

deleted_customers as (

    select
        id as customer_id,
        deleted as deleted_at

    from source

)

select * from deleted_customers

base配下に格納した定義を活用して、本来やりたかったことを実現：

with

customers as (

    select * from {{ ref('base_jaffle_shop__customers') }}

),

deleted_customers as (

    select * from {{ ref('base_jaffle_shop__deleted_customers') }}

),

join_and_mark_deleted_customers as (

    select
        customers.*,
        case
            when deleted_customers.deleted_at is not null then true
            else false
        end as is_deleted

    from customers

    left join deleted_customers on customers.customer_id = deleted_customers.customer_id

)

select * from join_and_mark_deleted_customers

✅ 異なるデータソース由来のものだが、スキーマが同じソースを結合させるような場合

ShopifyのようなSaaSプラットフォームを介して様々な地域で複数のeコマースプラットフォームを運営している場合、それらのデータは完全に同一のスキーマとなりますが、データは全て個別にウェアハウスにロードされる流れとなります。この場合、全てのショップデータが結合されていると後の処理が楽になるため、予め結合を済ませておきたいところです。ここにベースモデルの仕組みを適用させます。この使用例の詳細については下記エントリを御参照ください。

ステージングテーブルの生成を自動化するCodegenパッケージを使用

dbtにおけるModelの定義や設定等を学び、また当エントリで紹介されているようなベストプラクティスを学ぶ上では自ら手を動かしてその挙動等を確かめるのは非常に勉強になる点が多いです。ただその辺りを把握してしまえばあとはこういった作業は自動化させてしまいたいと思うのがエンジニアの常です。

dbtではそんな部分をサポートしてくれる仕組みが「codegenパッケージ」として提供されています。これを使うことで、全てのYAMLファイルとステージングモデルのボイラープレートを自動的に作成してくれます。実運用では必携のパッケージです。

ユーティリティフォルダの活用

ステージングフォルダに含まれない要素となりますが、「ユーティリティフォルダ」の仕組みを頭に入れておくと何かと役立ちます。models/utilitiesフォルダにマクロから生成したり、モデル化に役立つツールを提供するシードに基づいて生成した汎用モデルを保存できます。dbt_utilsパッケージを用いたdate_spineが分かりやすい例です。

• dbt-labs/dbt-utils: Utility functions for dbt projects.
• dbt - Package hub

dbtにおけるモデル開発のプロセス

モデル開発における通常一般的なプロセスとしては概ね以下のような流れです。

• 最終的なアウトプットのイメージが関係者と一致していることを確認するために、スプレッドシート等で設計をモックアウトすることから始める
• そのアウトプットを生成するSQLを記述し、どのテーブルが関係しているかを特定
• ロジック及び依存関係を把握できたら、必要となる全てのアトミックな要素(モデル＝テーブル)をステージング層に用意
• マートを生成するために作成したロジックに基づき、それらをまとめる
• マートのリファクタリングと最適化を行う

ロジックを分割し、分割した箇所を中間データ層のモデルに戻すことで、全てのモデルがクリーンで読み易くなり、テストも行い易くなります。

まとめ

という訳で、dbtプロジェクト構築に関するベストプラクティス第2弾としてデータウェアハウスにおける「ステージング層」に関する取り組み方の紹介でした。

ステージング層におけるデータの特徴や状況を踏まえた、明確で分かりやすい「べき」「べからず」がまとめられているな、という印象でした。後工程で色々と参照される重要な位置づけのレイヤーであるため、このあたりについてはデータウェアハウス設計のタイミングで考慮しておきたいものです。dbt側でかなり明確なルール、規約決めがなされていますので基本的にはこれをベースにデータウェアハウス側の「レイヤー分け」を進めても良いのかなと思いました。

• dbt の記事一覧 | DevelopersIO